/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.uca.sisbec.helpers.reportes;

import com.uca.sisbec.beans.ReporteBecGraduadosBean;
import com.uca.sisbec.forms.reportes.ReporteBecGraduadosForm;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 *
 * @author Alex
 */
public class ReporteBecGraduadosHelper {
    private Session session;

    public ReporteBecGraduadosHelper(Session session) {
        this.session = session;
    }
    
    public List<ReporteBecGraduadosBean> ConstruirReporteBecGraduadosDB(ReporteBecGraduadosForm data) {
        String hql = ConstruyeQueryBecGraduados(data);
        Query query = session.createQuery(hql);
        
        if(!(data.getProyecto().equals("N/A")))
            query.setString("proyecto", data.getProyecto());
        if(data.getCarrera() != 0)
            query.setInteger("carrera", data.getCarrera());
        if(data.getUniversidad() != 0)
            query.setInteger("universidad", data.getUniversidad());
        
        return new ArrayList<ReporteBecGraduadosBean>(query.list());
    }
    
    private String ConstruyeQueryBecGraduados(ReporteBecGraduadosForm data) {
        String selectHQL = "select new com.uca.sisbec.beans.ReporteBecGraduadosBean(b.carnet, b.nombres, b.apellidos, b.cum, b.fechaNac, c.carrera, cxb.fechaInicio, cxb.fechaFin, u.nombre, b.fondo)";
        String fromHQL = " from Becario b, Carrera c, CarreraXBecario cxb, Universidad u";
        String whereHQL = " where b.carnet = cxb.id.carnet and b.codCarrera = cxb.id.codCarrera and b.universidad.idUniv = cxb.id.idUniv and cxb.estado = 'G' and cxb.id.codCarrera = c.id.codCarrera and c.id.idUniv = u.idUniv";
        
        String masCondicionesHQL = "";
        
        if(!(data.getProyecto().equals("N/A")))
            masCondicionesHQL += " and b.fondo = :proyecto";
        if(data.getCarrera() != 0)
            masCondicionesHQL += " and c.id.codCarrera = :carrera";
        if(data.getUniversidad() != 0)
            masCondicionesHQL += " and u.idUniv = :universidad";
        
        String queryHQL = selectHQL + fromHQL + whereHQL + masCondicionesHQL;
        
        return queryHQL;
    }
}


